package itu.dk.bosk.ex9;

import java.sql.*;

/**
 * This is a class to test the Derby JDBC (BOSK ex 9.1)
 *
 * @author Michael Valentin
 */
public class TestDerby {
    static final String dbName = "TestDB";
    static final String url = "jdbc:derby:" + dbName + ";create=true";

    public static void main(String[] args) throws Exception {
        System.out.println("Began test");
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        Connection con = DriverManager.getConnection(url);
        Statement stmt = con.createStatement();

        //Make sure the table doesn't allready exist..
        try{
            stmt.execute("DROP TABLE customer");
        }catch(SQLException ex){
            //We can ignore this exception -> The table does not exist..
        }

        //Create the table..
        stmt.executeUpdate("CREATE TABLE customer ("+
                "id INTEGER NOT NULL PRIMARY KEY, "+
                "name VARCHAR(40), created DATE)");

        //Insert some customers..
        stmt.executeUpdate("INSERT INTO customer (id,name,created) VALUES("+
                "1,"+
                "'John Doe',"+
                "'2010-01-16')");
        stmt.executeUpdate("INSERT INTO customer (id,name,created) VALUES("+
                "2,"+
                "'Adam Smith',"+
                "'2010-02-18')");
        stmt.executeUpdate("INSERT INTO customer (id,name,created) VALUES("+
                "3,"+
                "'Vlademir Dobrevsky',"+
                "'2010-01-22')");
        //Could be done with a prepared statement..
        /*PreparedStatement insert = con.prepareStatement("INSERT INTO customer (id,name,created) VALUES(?,?,?)");
        insert.setInt(1, 1);
        insert.setString(2, "John Doe");
        insert.setDate(3, new Date(2011, 1, 12));
        insert.executeUpdate();*/

        //Read these customers again..
        ResultSet res = stmt.executeQuery("SELECT * FROM customer ORDER BY created DESC");
        System.out.println("Read these results: ");
        while(res.next()){
            System.out.println(res.getInt("id")+" "+res.getString("name"));
        }
        System.out.println("EOF Results..");
        System.out.println("Finished test");
    }
}
